/*
 * StatisticsCommand.java
 *
 * Created on October 5, 2007, 6:14 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package drb;

import java.io.BufferedReader;
import java.sql.*;

/**
 *
 * @author Asger
 */
public class StatisticsCommand extends PromptingCommand {
    
    /**
     * Creates a new instance of StatisticsCommand
     */
    public StatisticsCommand(BufferedReader input) {
        super(input);
    }
    
    public void execute() throws SQLException {
        
        // Get the interval to look in
        long start = promptDateTime("Enter the starting date:", getTimeNow());
        long end = promptDateTime("Enter the ending date:", getTimeNow()+ONE_DAY);
        
        // Get the number of tickets sold for this period
        String sql = "SELECT DISTINCT t.ticket_id FROM tickets t NATURAL JOIN ticketdue td NATURAL JOIN connections c WHERE " +
                "c.departure<="+end+" AND c.arrival>="+start;
        ResultSet result = connection.createStatement().executeQuery(sql);
        
        int numTickets = 0;
        int numTicketsWithSeat = 0;
        while (result.next()) {
            numTickets++;
            
            int id = result.getInt(1);
            
            // See if this seat has at least one seat booked
            sql = "SELECT EXISTS (SELECT * FROM reservations WHERE ticket_id="+id+")";
            ResultSet r2 = connection.createStatement().executeQuery(sql);
            r2.next();
            boolean hasSeat = r2.getBoolean(1);
            if (hasSeat)
            {
                numTicketsWithSeat++;
            }
        }
        
        // Get the number of seats booked for this period
        sql = "SELECT COUNT(*) FROM (SELECT ticket_id FROM reservations r NATURAL JOIN connections c WHERE " +
                "c.departure<="+end+" AND c.arrival>="+start+" GROUP BY ticket_id, seat_row, seat_position, wagon_number) AS x";
        result = connection.createStatement().executeQuery(sql);
        result.next();
        int numSeatsBooked = result.getInt(1);
        
        // Get the total number of seat seconds in the period
        sql = "SELECT SUM(c.arrival - c.departure) FROM seats s NATURAL JOIN wagons w NATURAL JOIN transports t NATURAL JOIN connections c WHERE " +
                "c.departure<="+end+" AND c.arrival>="+start+" AND w.wagon_type='S'";
        result = connection.createStatement().executeQuery(sql);
        result.next();
        int totalSeatSeconds = result.getInt(1);
        
        // Get the number of seat-connections that are booked
        sql += " AND EXISTS (SELECT * FROM reservations r WHERE r.departure=c.departure AND " +
                "r.train_number=c.train_number AND r.wagon_number=s.wagon_number AND r.seat_row=s.seat_row AND r.seat_position=s.seat_position)";
        result = connection.createStatement().executeQuery(sql);
        result.next();
        int totalSeatSecondsUsed = result.getInt(1);
        
        // Get the total number of seat seconds in the period
        sql = "SELECT COUNT(*) FROM seats s NATURAL JOIN wagons w NATURAL JOIN transports t NATURAL JOIN connections c WHERE " +
                "c.departure<="+end+" AND c.arrival>="+start+" AND w.wagon_type='S'";
        result = connection.createStatement().executeQuery(sql);
        result.next();
        int totalSeats = result.getInt(1);
        
        // Get the number of seat-connections that are booked
        sql += " AND EXISTS (SELECT * FROM reservations r WHERE r.departure=c.departure AND " +
                "r.train_number=c.train_number AND r.wagon_number=s.wagon_number AND r.seat_row=s.seat_row AND r.seat_position=s.seat_position)";
        result = connection.createStatement().executeQuery(sql);
        result.next();
        int totalSeatsUsed = result.getInt(1);
        
        int occupationTime = 0;
        if (totalSeatSeconds > 0)
            occupationTime = (1000*totalSeatSecondsUsed + 500) / (totalSeatSeconds);
        
        int occupationSpace = 0;
        if (totalSeats > 0)
            occupationSpace = (1000*totalSeatsUsed + 500) / (totalSeats);
        
        System.out.println("-------------------------------");
        System.out.println("Start date:                " + formattedDateTime(start));
        System.out.println("End date:                  " + formattedDateTime(end));
        System.out.println("Number of tickets sold:    " + numTickets + " (" + numTicketsWithSeat +" with at least one seat booked)");
        System.out.println("Number of seats booked:    " + numSeatsBooked);
        System.out.println("Total seat/bed hours:      " + (totalSeatSeconds / 60 / 60));
        System.out.println("Total seat/bed hours used: " + (totalSeatSecondsUsed / 60 / 60));
        System.out.println("Time occupation %:         " + (occupationTime/10) +"." + (occupationTime%10) + "%");
        System.out.println("Total transport capacity:  " + (totalSeats));
        System.out.println("Total transport cap. used: " + (totalSeatsUsed));
        System.out.println("Transport occupation %:    " + (occupationSpace/10) +"." + (occupationSpace%10) + "%");
        System.out.println("-------------------------------");
        
        // NOTE:
        // We cannot correctly solve the last part of the assignment,
        //    #  Print for each route and between 2 dates, the percentage of occupation. 
        // because of the way we represent connections. Changing it now is
        // out of the question with a deadline of one week.
        
    }
    
}




















